<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<%@ page import="elibrary.*"%>
<%@ page import="java.sql.*"%>
<%@ page import="java.util.*"%>
<%@ include file="config.jsp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>MyLibrary | Cron</title>
    </head>
    <body>
        <%
                java.util.Date curDate = new java.util.Date();
                Calendar cal = Calendar.getInstance();
                java.util.Date date;
	
                Connection con=DaoConnection.getcon(connectURL, DBuser, DBpass);
                Statement st=con.createStatement();
                ResultSet rs=st.executeQuery("SELECT * FROM COPY WHERE ISSUED = 1");
                while(rs.next()){
                        date = rs.getDate("DUEDATE");
                        if(date != null ) {
                                cal.setTime(date);
                                cal.add(Calendar.DAY_OF_YEAR, -4);
                                date = cal.getTime();
			
                                curDate.setTime(0);
                                date.setTime(0);
                                if (curDate.equals(date)){
                                        //send mail
                                String query = "SELECT * FROM USERS WHERE UID="+rs.getInt("UID");
                                PreparedStatement ps=con.prepareStatement(query);
                                ResultSet user=ps.executeQuery();
                                user.next();
                                query = "SELECT * FROM BOOK WHERE BOOKID = '"+rs.getString("BOOKID")+"'";
                                ps=con.prepareStatement(query);
                                        ResultSet bookDetail = ps.executeQuery();
                                        bookDetail.next();
		    	
                                String to = user.getString("EMAIL");
                                        String from = siteEmail;
                                        String subject = "Reminder for due date of a book";
                                        String text = "Dear user,\nThe due date of the following book is near, kindly submit it.";
                                        text = text + "\n\nBook Details:";
                                        text = text + "\nBOOK ID: "+rs.getString("BOOKID");
                                        text = text + "\nBOOK NAME: "+bookDetail.getString("BOOKNAME");
                                        text = text + "\nAUTHOR: "+bookDetail.getString("AUTHOR");
                                        text = text + "\nPUBLISHER: "+bookDetail.getString("PUBLISHER");
                                        text = text + "\nDUE DATE: "+rs.getDate("DUEDATE");
				
                                        Mailer mailer = new Mailer();
                                        mailer.sendmail(to, from, subject, text);
				
                                        out.println("This page is not supposed to have any content :).");
                                }
                        }
                }
        %>
    </body>
</html>